#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

sql="
create table if not exists jtp_app_dwd.tmp_event_log_cleaned as
select *
from jtp_app_ods.ods_event_app_log
where dt = '$data_date'
and deviceid is not null and length(deviceid) != 0
and eventid is not null and length(eventid) != 0
and sessionid is not null and length(sessionid) != 0
and properties is not null and size(properties) != 0;

create table if not exists jtp_app_dwd.tmp_event_log_session as
with t1 as (
    select *,
           (\`timestamp\` - lag(\`timestamp\`,1,0) over (partition by deviceid order by \`timestamp\`)) /1000/60 as diff_ts
    from jtp_app_dwd.tmp_event_log_cleaned
), t2 as (
    select
        *,
        if(diff_ts >= 10,concat(deviceid,'_',\`timestamp\`),null) as start_sessionid
    from t1
), t3 as (
    select
        *,
        first_value(start_sessionid) over (partition by deviceid order by \`timestamp\`) as new_sessionid
    from t2
)
select
    account, appid, appversion, carrier, deviceid, devicetype, eventid, ip, latitude, longitude,
    nettype, osname, osversion, properties, releasechannel, resolution, sessionid, \`timestamp\`,
    new_sessionid
from t3;


add jar hdfs://node101:8020/day0417-1.0-SNAPSHOT.jar;
create temporary function myGeoHash as 'com.bw.myGeoHash';

create table if not exists jtp_app_dwd.tmp_event_log_dic as
with t1 as (
    select *,
           myGeoHash(t1.latitude,t1.longitude,6) geohash
    from jtp_app_dwd.tmp_event_log_session t1
), t2 as (
    select * from jtp_app_dim.dim_md_areas where dt = '$data_date'
)
select
    account, appid, appversion, carrier, deviceid, devicetype, eventid, ip, latitude, longitude,
    nettype, osname, osversion, properties, releasechannel, resolution, sessionid,
    \`timestamp\`, new_sessionid, geohash,province, city, district, street
from t1
left join t2 on t1.geohash = t2.geo_hash
where province is not null;

insert into table jtp_app_dwd.dwd_event_log_detail partition (dt = '$data_date')
select * from jtp_app_dwd.tmp_event_log_dic;
"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${sql}"